/**
 * Created by Mark_ren on 2016/11/28.
 * 苟富贵,無相忘
 */
var moment = require("moment");
var co = require('co');
var xlsx = require('node-xlsx');
var fs = require('fs');
var formidable = require('formidable');
var nodeExcel = require('excel-export');
var seqModule = require('../../../routes/createModules');
var seq = new seqModule();
var util = require('../../../common/util');

var memberMeetController = {
    getMemberMeetPage: function (req, res) {
        co(function () {
            var page = parseInt(req.query.page);
            var nameSql = "";
            if (req.query.match != undefined) {
                if (req.query.match == "2") {
                    nameSql = "select count(*) from member where name = met.name";
                } else {
                    nameSql = "select count(*) from member where tel = met.tel";
                }
            } else {
                nameSql = "select count(*) from member where tel = met.tel";
            }
            var memberMetSql = "select met.*,(" + nameSql + ") as isMember,mem.name as memName,mem.hospital_name as hospitalName from member_meet as met ";

            if (req.query.match != undefined) {
                if (req.query.match == "2") {
                    memberMetSql += " left join member as mem on met.name = mem.name where 1=1";
                } else {
                    memberMetSql += " left join member as mem on met.tel = mem.tel where 1=1"
                }
            } else {
                memberMetSql += " left join member as mem on met.tel = mem.tel where 1=1"
            }
            if (req.query.meetNum != undefined) {
                memberMetSql += " and met.meet_num = '" + req.query.meetNum + "'";
            }
            if (req.query.name != undefined) {
                memberMetSql += " and (met.name like '%" + req.query.name + "%' or met.tel like '%" + req.query.name + "%' or met.hospital like '%" + req.query.name + "%' or met.hos_office like '%" + req.query.name + "%')";
            }
            if (req.query.resName != undefined) {
                memberMetSql += " and (met.res_name like '%" + req.query.resName + "%' or met.res_tel like '%" + req.query.resName + "%' )";
            }

            if (req.query.match != undefined) {
                if (req.query.match == "2") {
                    memberMetSql += " and met.is_register = 0 having isMember = 1";
                }
            }
            var memberMetCount = memberMetSql;
            if (req.query.page != undefined && req.query.rows != undefined) {
                memberMetSql += " limit " + parseInt(req.query.page) + "," + parseInt(req.query.rows);
            }
            seq.sequelize.query(memberMetCount).spread(function (result) {
                seq.sequelize.query(memberMetSql).spread(function (resultData) {
                    res.json({ "success": true, "msg": "查询成功", "page": page, "total": result.length, "result": resultData });
                })
            });
        }).catch(function (e) {
            res.json({ 'success': false, 'msg': '查询失败' });
            console.log(e);
        })

    },
    updateIsRegisterByTel: function (req, res) {
        co(function () {
            var sql = "select met.id,(select count(*) from member where tel = met.tel) as isMember from member_meet as met where 1=1 ";

            if (req.query.meetNum != undefined) {
                sql += " and met.meet_num = '" + req.query.meetNum + "'";
            }
            sql += " and met.is_register = 0 having isMember = 1";
            seq.sequelize.query(sql).spread(function (result) {
                var arr = [];
                result.forEach(function (val) {
                    arr.push(val.id);
                });
                if (arr.length > 0) {
                    seq.memberMeetModule.update({ isRegister: 1 }, { where: { id: arr } }).then(function (result) {
                        res.json({ 'success': true, 'msg': '修改成功' });
                    })
                } else {
                    res.json({ 'success': true, 'msg': '修改成功' });
                }

            })
        }).catch(function (e) {
            res.json({ 'success': false, 'msg': '修改失败' });
            console.log(e);
        })
    },
    updateById: function (req, res) {
        co(function () {
            seq.memberMeetModule.update(req.body, { where: req.params }).then(function (result) {
                res.json({ 'success': true, 'msg': '修改成功' });
            })
        }).catch(function (e) {
            res.json({ 'success': false, 'msg': '修改失败' });
            console.log(e);
        })
    },
    exportTemplate: function (req, res) {
        var data = [['姓名', '手机号', '性别', '邮箱', '医院', '科室', '职务', '代表姓名', '代表手机号', '囯区', '大区', '地区', '会议编号']];
        // var data1 = [[4, 5, 6], ['ceshi', true, '', 'sheet2'], ['node', 'excel', new Date('2016-11-15T14:22Z'), '100'], ['ceshi2', '', 'quxia']];
        var buffer = xlsx.build([{ name: "客户名单", data: data }]);
        fs.writeFileSync('public/data/excel/kehumoban.xlsx', buffer, 'binary');
        res.send('export successfully!');
    },
    importData: function (req, res) {
        co(function () {
            if (req.method.toLowerCase() == 'post') {
                // parse a file upload
                var form = new formidable.IncomingForm();
                // form.uploadDir = "public/img";
                form.type = true;
                form.on("progress", function (bytesReceived, bytesExpected) {
                    console.log("progress!" + bytesReceived + " B____" + (bytesExpected / 1024 / 1024).toFixed(2) + " MB");
                    // 百分比
                    var percent = Math.round(bytesReceived / bytesExpected * 100);
                    console.log("percent:" + percent + "%");
                    // 存入
                    //sessions.setSession(req,res,opts);
                });
                form.parse(req, function (err, fields, files) {
                    var meetNo = fields.meetNum;
                    var fileName = files.importExcel.name;
                    if (fileName != null && fileName != "") {
                        fileName = fileName.substring(fileName.lastIndexOf(".") + 1);
                        if (fileName == "xlsx") {
                            var obj = xlsx.parse(files.importExcel.path);
                            var resutData = obj[0].data;
                            var insertSql = "insert into member_meet (name,tel,sex,email,hospital,hos_office,post,res_name,res_tel,daqu,diqu,meet_num,is_register,create_time) values ";
                            for (var i = 1; i < resutData.length; i++) {
                                var tel = "";
                                if (resutData[i][1] != null && resutData[i][1] != undefined) {
                                    tel = resutData[i][1];
                                }
                                var sex = "";
                                if (resutData[i][2] != null && resutData[i][2] != undefined) {
                                    sex = resutData[i][2];
                                }
                                var email = "";
                                if (resutData[i][3] != null && resutData[i][3] != undefined) {
                                    email = resutData[i][3];
                                }
                                var hospital = "";
                                if (resutData[i][4] != null && resutData[i][4] != undefined) {
                                    hospital = resutData[i][4];
                                }
                                var office = "";
                                if (resutData[i][5] != null && resutData[i][5] != undefined) {
                                    office = resutData[i][5];
                                }
                                var post = "";
                                if (resutData[i][6] != null && resutData[i][6] != undefined) {
                                    post = resutData[i][6];
                                }
                                var resName = "";
                                if (resutData[i][7] != null && resutData[i][7] != undefined) {
                                    resName = resutData[i][7];
                                }
                                var resTel = "";
                                if (resutData[i][8] != null && resutData[i][8] != undefined) {
                                    resTel = resutData[i][8];
                                }
                                var daqu = "";
                                if (resutData[i][10] != null && resutData[i][10] != undefined) {
                                    daqu = resutData[i][10];
                                }
                                var diqu = "";
                                if (resutData[i][11] != null && resutData[i][11] != undefined) {
                                    diqu = resutData[i][11];
                                }
                                var meetNum = "";
                                if (resutData[i][12] != null && resutData[i][12] != undefined) {
                                    meetNum = resutData[i][12];
                                }
                                var isRegister = 0;
                                var createTime = util.getNowTime();
                                insertSql += "('" + resutData[i][0] + "','" + tel + "','" + sex + "','" + email + "','" + hospital + "','" + office + "','" + post + "','" + resName + "','" + resTel + "','" + daqu + "','" + diqu + "','" + meetNum + "','" + isRegister + "','" + createTime + "'),";
                            }
                            insertSql = insertSql.substring(0, insertSql.lastIndexOf(","));
                            console.log(insertSql);
                            seq.sequelize.query(insertSql).spread(function (data) {
                                res.redirect(util.getIP() + 'qdMeet/qdMemberMeetList.html?meetNum=' + meetNo);
                            })
                        } else {
                            res.json({ 'success': false, 'msg': '上传文件不正确！' });
                        }
                    }
                });
            }
        }).catch(function (e) {
            res.json({ 'success': false, 'msg': '添加失败' });
            console.log(e);
        })
    },
    exportData: function (req, res) {
        co(function () {
            var params = {};
            if (req.query.meetNum != undefined) {
                params.meetNum = req.query.meetNum;
            }
            seq.memberMeetModule.findAll({ where: params }).then(function (result) {
                var conf = {};
                conf.stylesXmlFile = "styles.xml";//注意：这个文件需要去我刚刚发的那个网址上下载一个下来
                conf.name = "kehumingdan";
                var vac = new Array();
                conf.cols = [
                    {
                        caption: 'id',
                        type: 'string',
                        width: 10.6
                    }, {
                        caption: '姓名',
                        type: 'string',
                        width: 10
                    }, {
                        caption: '性别',
                        type: 'string',
                        width: 10
                    }, {
                        caption: '邮箱',
                        type: 'string',
                        width: 10
                    }, {
                        caption: '医院',
                        type: 'string',
                        width: 10
                    }, {
                        caption: '科室',
                        type: 'string',
                        width: 10
                    }, {
                        caption: '职务',
                        type: 'string',
                        width: 10
                    }, {
                        caption: '代表姓名',
                        type: 'string',
                        width: 10
                    }, {
                        caption: '代表手机号',
                        type: 'string',
                        width: 10
                    }, {
                        caption: '囯区',
                        type: 'string',
                        width: 10
                    }, {
                        caption: '大区',
                        type: 'string',
                        width: 10
                    }, {
                        caption: '地区',
                        type: 'string',
                        width: 10
                    }, {
                        caption: '注册状态',
                        type: 'string',
                        width: 10
                    }, {
                        caption: '会议编号',
                        type: 'string',
                        width: 10
                    }, {
                        caption: '创建时间',
                        type: 'string',
                        width: 10
                    }
                ];
                // var count = 0;

                for (var i = 0; i < result.length; i++) {
                    var val = result[i];
                    var temp = new Array();
                    temp[0] = val.id + "";
                    temp[1] = val.name || "";
                    temp[2] = val.sex || "";
                    temp[3] = val.email || "";
                    temp[4] = val.hospital || "";
                    temp[5] = val.hosOffices || "";
                    temp[6] = val.post || "";
                    temp[7] = val.resName || "";
                    temp[8] = val.resTel || "";
                    temp[9] = val.guoqu || "";
                    temp[10] = val.daqu || "";
                    temp[11] = val.diqu || "";
                    var isReg = "";
                    if (val.isRegister == 0) {
                        isReg = "未注册";
                    } else if (val.isRegister == 1) {
                        isReg = "已注册";
                    } else {
                        isReg = "不参加";
                    }
                    temp[12] = isReg || "";
                    temp[13] = val.meetNum || "";
                    temp[14] = val.createTime || "";
                    //temp[10] = (trasations[i].state) == "0"?"未付款或未持久化":"已付款或已持久化";
                    vac.push(temp);

                }

                conf.rows = vac;//conf.rows只接受数组
                var result = nodeExcel.execute(conf);
                res.setHeader('Content-Type', 'application/vnd.openxmlformats');
                res.setHeader("Content-Disposition", "attachment; filename=" + "kehumingdan.xlsx");
                res.end(result, 'binary');
            })
        }).catch(function (e) {
            res.json({ 'success': false, 'msg': '导出失败' });
            console.log(e);
        })
    },
    addMemberMeet: function (req, res) {
        co(function () {
            req.body.createTime = util.getNowTime();
            seq.memberMeetModule.create(req.body).then(function (result) {
                res.json({ 'success': true, 'msg': '添加成功' });
            })
        }).catch(function (e) {
            res.json({ 'success': false, 'msg': '添加失败' });
            console.log(e);
        })
    }


}
module.exports = memberMeetController;